package ewa

import (
    "database/sql"
    "fmt"
    //"time"
    _ "github.com/mattn/go-sqlite3"
)

func checkErr(err error) {
    if err != nil {
  		l:=fmt.Sprintf("-Sqlite3/> %v", err)
      	Log(l)
    }
}

func SQLite3_Open_DBfile(dbfile string) *sql.DB {
    db, err := sql.Open("sqlite3", dbfile)
    if err != nil{
      l:=fmt.Sprintf("-Sqlite3/> Open data file '%s' failed!", dbfile)
      Log(l)
    }else {
      l:=fmt.Sprintf("-Sqlite3/> Open data file '%s' successfully.", dbfile)
      Log(l)
    }

    return db
}

/*
 应用启动时, 默认创建
 */
func  SQLite3_AuthTab_Create(db *sql.DB, UserAuthTab string) {
  SQL := fmt.Sprintf(
`CREATE TABLE IF NOT EXISTS "%s"(
   "UserId" INTEGER PRIMARY KEY AUTOINCREMENT,
   "Username" VARCHAR(100) NOT NULL UNIQUE,
   "Password" VARCHAR(40) NOT NULL,
   "Email" VARCHAR(100) NOT NULL,
   "Hiredate" DATE, 
   "Regtime" DATETIME
);` , UserAuthTab)

    db.Exec(SQL)
    N,_ := SQLite3_AuthTab_QueryUser(db, UserAuthTab, DefaultAdminUsername)
    if N == 0 {
      SQLite3_AuthTab_InsertNew(db, UserAuthTab, DefaultAdmin())
    }
}


func SQLite3_Close(db *sql.DB) {
    db.Close()
    l:=fmt.Sprintf("-Sqlite3/> Close data file.")
    Log(l)
}


/*
 判断用户是否存在
*/
func SQLite3_AuthTab_QueryUser(db *sql.DB, UserAuthTab string, Username string) (int,error) {
    SQL := fmt.Sprintf("SELECT Username From %s where Username = '%s';", UserAuthTab, Username)
    rows, err := db.Query(SQL)
    checkErr(err)
    var User string = ""
    var cnt int = 0
    for rows.Next() {
        err = rows.Scan(&User)
        checkErr(err)
        if User != "" {
            cnt++
            User = ""
        }
    }

    l:=fmt.Sprintf("-Sqlite3/> Find [%d] User through 'Username[%s]'", cnt, Username )
    Log(l)

    return cnt,err
}


/*
 根据用户名查询密码字符串
 */
func SQLite3_AuthTab_QueryPassword(db *sql.DB, UserAuthTab string, Username string) (string,error) {
    SQL := fmt.Sprintf("SELECT Password From %s where Username = '%s';", UserAuthTab, Username)
    rows, err := db.Query(SQL)
    checkErr(err)
    var Password string
    for rows.Next() {
        err = rows.Scan(&Password)
        checkErr(err)
    }

    return Password,err
}

/*
 新增一个注册用户信息
 */
func SQLite3_AuthTab_InsertNew(db *sql.DB, UserAuthTab string, newdata UserAuthTabDesc) (int64, int64, error) {
    //newdata.Email = newdata.Username+"@supor.com"
    //newdata.Hiredate = "2020-05-01"
    //newdata.Regtime = time.Now().Format("2006-01-02 15:04:05")

    var err error 
    var stmt *sql.Stmt
    var res sql.Result

    SQL := fmt.Sprintf(`INSERT INTO %s(Username, Password, Email, Hiredate, Regtime) VALUES(
                        ?,?,?,?,?)`, UserAuthTab)
    
    stmt, err = db.Prepare(SQL)
    checkErr(err)
    res, err = stmt.Exec(newdata.Username,
                        newdata.Password,
                        newdata.Email,
                        newdata.Hiredate,
                        newdata.Regtime)
    var id int64 = -1
    var rowCnt int64 = 0
    checkErr(err)
    if err == nil {
        id, err = res.LastInsertId()
        checkErr(err)
        if err == nil {
            rowCnt, err = res.RowsAffected()
            // fmt.Println(id)
        }
    }
    
    if stmt != nil {
        stmt.Close()
    }   

    return id, rowCnt, err
}


/*
 删除一个用户信息
 */
func SQLite3_AuthTab_DeleteOne(db *sql.DB, UserAuthTab string, Username string) (int64, error) {
    SQL := fmt.Sprintf("DELETE FROM %s WHERE Username = ?;", UserAuthTab)
    stmt, err := db.Prepare(SQL)
    checkErr(err)
    
    res, err := stmt.Exec(Username)
    checkErr(err)

    rowCnt, err := res.RowsAffected()
    checkErr(err)
    
    defer stmt.Close()

    return rowCnt, err
}

/*
 更新一个用户信息
 */
func SQLite3_AuthTab_UpdatePassword(db *sql.DB, UserAuthTab string, Username string, Password string) (int64, error) {
    SQL := fmt.Sprintf("UPDATE %s SET Password = ? WHERE Username = ?;", UserAuthTab)
    stmt, err := db.Prepare(SQL)
    checkErr(err)

    res, err := stmt.Exec(Password, Username)
    checkErr(err)

    rowCnt, err := res.RowsAffected()

    defer stmt.Close()

    return rowCnt, err
}